package com.tpc.server.systeme.utils;

import com.google.appengine.api.rdbms.AppEngineDriver;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.logging.Logger;
import java.util.Properties;

public class DatabaseManager extends RemoteServiceServlet {

    private Properties propriete;
    private Connection objConnection = null;
    private PreparedStatement preparedStmt = null;
    private final static Logger objLog = Logger.getLogger(DatabaseManager.class.getName());

    public DatabaseManager() {
        
    }

    public DatabaseManager(Properties proprieteParam) {
        propriete = proprieteParam;
    }

    public Connection getConnection() throws Exception {
        try {
        	DriverManager.registerDriver(new AppEngineDriver());
            if (objConnection == null) {
            	// Needs to be inside DataBaseManager so GWT can switch between Google Cloud SQL (PROD) and MySQL Local (Dev)
                objConnection = DriverManager.getConnection("jdbc:google:rdbms://pme123.com:pme123-test:tpctbd/timesheet3");
            }
            return objConnection;
        }
        catch (Exception objEx) {
            objLog.info("1 : "+objEx.getMessage());
            throw objEx;
        }
    }

    public ResultSet executeQuery(String sqlQuery, String[] parameters) throws Exception {
        try {
            ResultSet objRS = null;
            objLog.info(sqlQuery);
            preparedStmt = getConnection().prepareStatement(sqlQuery,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            if (parameters != null) {
                for (int i = 0; i < parameters.length; i++) {
                    String strParameter = modifySpecialCharacter(parameters[i]);
                    preparedStmt.setString(i+1,strParameter);
                }
            }
            objRS = preparedStmt.executeQuery();
            return objRS;
        }
        catch (Exception objEx) {
            objLog.info("2 : "+objEx.getMessage());
            throw objEx;
        }
    }

    public int executeUpdate(String sqlQuery, String[] parameters) throws Exception {
        try {
            int intRow = -1;
            preparedStmt = getConnection().prepareStatement(sqlQuery,java.sql.Statement.RETURN_GENERATED_KEYS);
            // LOG DEBUG write query
            objLog.info(sqlQuery);
            if (parameters != null) {
                for (int i = 0; i < parameters.length; i++) {
                    String strParameter = parameters[i];
                    if (strParameter != null) {
                        strParameter = modifySpecialCharacter(strParameter);
                    }
                    preparedStmt.setString(i+1,strParameter);
                }
            }
            intRow = preparedStmt.executeUpdate();
            return intRow;
        }
        catch (Exception objEx) {
            objLog.info(objEx.getMessage());
            throw objEx;
        }
    }

    public boolean closeConnection() {
        try {
            if (objConnection != null) {
                if (!objConnection.isClosed()) {
                    objConnection.close();
                }
            }
            return true;
        }
        catch (Exception objEx) {
            objLog.info(objEx.getMessage());
            return false;
        }
    }

    public int getLastInsertedId() {
        try {
            int lastId = -1;
            java.sql.ResultSet objRSii = preparedStmt.getGeneratedKeys();
            if (objRSii != null) {
                if (objRSii.next()) {
                    lastId = objRSii.getInt(1);
                }
            }
            return lastId;
        }
        catch (Exception objEX) {
            objLog.info(objEX.getMessage());
            return -1;
        }
    }

    private String modifySpecialCharacter(String strValue) {
    // @TODO: should be changed to modify Special Character according to SQL Vendor
    // @TODO: should check for SQL injection sequence and <script> tag
        //return strValue.replaceAll("'", "''");
        return strValue;
    }
	
}
